package com.hnkywl.sts.dao.report;

import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import oracle.jdbc.OracleCallableStatement;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.*;
import java.util.Date;

/**
 * Created with IntelliJ IDEA.
 * User: Administrator
 * Date: 13-6-15
 * Time: 下午9:13
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class PjkcDao extends SimpleHibernateTemplate {
    /**
     * 票据库存报表应收
     *
     * @param gsid
     * @param year
     * @param month
     * @return
     */
    public Map<String, Object> pjkcysList(Long gsid, Integer year, Integer month) {
        if (gsid == null)
            gsid = -1L;
        Connection con = null;
        CallableStatement call = null;
        ResultSet rs = null;
        Integer total = 0;
        try {
            con = getSession().connection();
            call = con.prepareCall("{Call RPT_CW_PJKCYS(?,?,?,?)}");
            call.setInt(1, year);
            call.setInt(2, month);
            call.setFloat(3, gsid);
            call.registerOutParameter(4, oracle.jdbc.OracleTypes.CURSOR);
            call.execute();
            //====================================
            rs = ((OracleCallableStatement) call).getCursor(4);
            ResultSetMetaData resultsMetaData = rs.getMetaData();
            int columnCount = resultsMetaData.getColumnCount();
            Map<String, Object> result = new HashMap<String, Object>();
            List<Map> rows = new ArrayList<Map>();
            List<Map> footer = new ArrayList<Map>();
            while (rs.next()) {
                Map<String, Object> r = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = resultsMetaData.getColumnName(i).toLowerCase();
                    r.put(columnName, rs.getObject(i));
                }
                if ("1".equals(r.get("flag").toString()))
                    rows.add(r);
                else
                    footer.add(r);
            }
            result.put("rows", rows);
            result.put("footer", footer);
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            closeResource(rs, call, con);
        }
    }

    /**
     * 票据库存报表应付
     *
     * @param gsid
     * @param year
     * @param month
     * @return
     */
    public Map<String, Object> pjkcyfList(Long gsid, Integer year, Integer month) {
        if (gsid == null)
            gsid = -1L;
        Connection con = null;
        CallableStatement call = null;
        ResultSet rs = null;
        try {
            con = getSession().connection();
            call = con.prepareCall("{Call RPT_CW_PJKCYF(?,?,?,?)}");
            call.setInt(1, year);
            call.setInt(2, month);
            call.setLong(3, gsid);
            call.registerOutParameter(4, oracle.jdbc.OracleTypes.CURSOR);
            call.execute();
            //====================================
            rs = ((OracleCallableStatement) call).getCursor(4);
            ResultSetMetaData resultsMetaData = rs.getMetaData();
            int columnCount = resultsMetaData.getColumnCount();
            Map<String, Object> result = new HashMap<String, Object>();
            List<Map> rows = new ArrayList<Map>();
            List<Map> footer = new ArrayList<Map>();
            while (rs.next()) {
                Map<String, Object> r = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = resultsMetaData.getColumnName(i).toLowerCase();
                    r.put(columnName, rs.getObject(i));
                }
                if ("1".equals(r.get("flag").toString()))
                    rows.add(r);
                else
                    footer.add(r);
            }
            result.put("rows", rows);
            result.put("footer", footer);
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            closeResource(rs, call, con);
        }
    }


    public Map<String, Object> pjkcDetail(Long gsid, Integer sfType, Integer type, Integer status, Date startDate, Date endDate, Integer pageNo, Integer pageSize) {
        if (gsid == null)
            gsid = -1L;
        Connection con = null;
        CallableStatement call = null;
        ResultSet rs = null;
        Integer total = 0;
        Double hjje = 0.0;
        if (startDate == null)
            startDate = new java.util.Date();
        if (endDate == null)
            endDate = new java.util.Date();
        Map<String, Object> result = new HashMap<String, Object>();
        try {
            con = getSession().connection();
            call = con.prepareCall("{Call RPT_CW_PJKC_DETAIL(?,?,?,?,?,?,?,?,?,?,?)}");
            call.setLong(1, gsid);
            call.setInt(2, sfType);
            call.setInt(3, type);
            call.setInt(4, status);
            call.setDate(5, new java.sql.Date(startDate.getTime()));
            call.setDate(6, new java.sql.Date(endDate.getTime()));
            call.setInt(7, (pageNo - 1) * pageSize);
            call.setInt(8, pageNo * pageSize);
            call.registerOutParameter(9, Types.NUMERIC);
            call.registerOutParameter(10, Types.NUMERIC);
            call.registerOutParameter(11, oracle.jdbc.OracleTypes.CURSOR);
            call.execute();
            //====================================
            total = call.getInt(9);
            hjje = call.getDouble(10);
            rs = ((OracleCallableStatement) call).getCursor(11);
            ResultSetMetaData resultsMetaData = rs.getMetaData();
            int columnCount = resultsMetaData.getColumnCount();
            List<Map> rows = new ArrayList<Map>();
            while (rs.next()) {
                Map<String, Object> r = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = resultsMetaData.getColumnName(i).toLowerCase();
                    r.put(columnName, rs.getObject(i));
                }
                rows.add(r);
            }
            result.put("total", total);
            result.put("hjje", hjje);
            result.put("rows", rows);
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            closeResource(rs, call, con);
        }
    }


    private void closeResource(ResultSet rs, Statement s, Connection con) {
        if (rs != null)
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (s != null)
            try {
                s.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (con != null)
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
    }
}
